package zunge.twitterdel.typostat;

import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import zunge.tools.DbConn;
import zunge.twitterdel.bean.DelTweet;
import zunge.twitterdel.bean.DelTweetDAO;
import zunge.twitterdel.bean.RepostTweetDAO;
import zunge.twitterdel.bean.TweetPair;
import zunge.twitterdel.bean.TweetPairDAO;
import zunge.twitterdel.bean.TweetPairDistinct;
import zunge.twitterdel.bean.TypoTweetDAO;

public class GetTypoPairs {
	
	static String farHost = "rincewind.isr.cs.cmu.edu";//"128.237.116.53";
	static String farDb = "deletedtweets";
	static String farUser = "bliu1";
	static DbConn farConn;
	
	static String localHost = "localhost";
	static String localDb = "typos";
	static String localUser = "root";
	static DbConn localConn;
	
	static int continuous_step = 2; // Tweet A1, A2, A3:  A1 and A3 can be adjacent
	
	static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.0");
	
	public static void main(String args[]){
		//getDelTweets();
		//getTweetsPairs();
		getTypoPairs();
	}
	
	/**
	 * Get all the deleted tweets
	 */
	public static void getDelTweets(){
		try{
			System.out.println("GET DEL TWEETS");
			farConn = new DbConn(farHost,farDb,farUser,null);
			
			String query = 
				"SELECT " +
					"a.userID, " +
					"a.tweetID, " +
					"a.tweetDate, " +
					"a.actualTweet " +
				"FROM " +
					"tweetuser a, tweetuser b " +
				"WHERE " +
					"a.tweetID = b.tweetID " +
					"AND a.tweetDate IS NOT NULL " +
					"AND b.tweetDate IS NULL";
			ResultSet rs = farConn.executeQuery(query);

			List<DelTweet> delTweets = new ArrayList<DelTweet>();
			while(rs.next()){
				DelTweet delTweet = new DelTweet();
				delTweet.setUserID(rs.getLong("userID"));
				delTweet.setTweetID(rs.getLong("tweetID"));
				delTweet.setTweetDate(sdf.parse(rs.getString("tweetDate")));
				delTweet.setActualTweet(rs.getString("actualTweet"));
				delTweets.add(delTweet);
			}
			farConn.close();
			System.out.println("SIZE="+delTweets.size());
			
			System.out.println("\nWrite to local");
			
			String jdbcURL = "jdbc:mysql://"+localHost+"/"+localDb;
			
			DelTweetDAO dao = new DelTweetDAO(jdbcURL,localUser,null);
			for(int i = 0;i < delTweets.size();++i){
				dao.create(delTweets.get(i));
			}
			
			System.out.println("\nDone");
			
			
		}catch(Exception e){e.printStackTrace();}
	}
	
	
	
	
	
	
	
	
	
	
	/**
	 * Get all continuous tweet pairs of same users
	 */
	public static void getTweetsPairs(){
		try{
			System.out.println("GET TWEET PAIRS");
			farConn = new DbConn(farHost,farDb,farUser,null);
			
			String query = 
				"SELECT * FROM " +
					"tweetuser " +
				"WHERE " +
					"tweetDate IS NOT NULL " +
				"ORDER BY userID ASC, tweetDate ASC ";
			ResultSet rs = farConn.executeQuery(query);
			
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.0");
			
			List<TweetPair> tweetPairs = new ArrayList<TweetPair>();
			
			long curUserID = -1;
			List<Tweet> userTweets = null;
			while(rs.next()){
				long userID = rs.getLong("userID");
				long tweetID = rs.getLong("tweetID");
				String actualTweet = rs.getString("actualTweet");
				java.util.Date tweetDate = sdf.parse(rs.getString("tweetDate"));
				Tweet tweet = new Tweet(userID,tweetID,actualTweet,tweetDate);

				if(userID != curUserID){
					if(userTweets != null && userTweets.size()>1){
						addNewUser(userTweets, tweetPairs);
					}
					curUserID = userID;
					userTweets = new ArrayList<Tweet>();
					userTweets.add(tweet);
				}
				else{
					userTweets.add(tweet);
				}
			}
			if(userTweets.size()>1){
				addNewUser(userTweets,tweetPairs);
			}
		
			farConn.close();
			
			System.out.println("\nWrite to local");
			
			String jdbcURL = "jdbc:mysql://"+localHost+"/"+localDb;
			
			TweetPairDAO dao = new TweetPairDAO(jdbcURL,localUser,null);
			for(int i = 0;i < tweetPairs.size();++i){
				dao.create(tweetPairs.get(i));
			}
			
			System.out.println("\nDone");
			
		}catch(Exception e){e.printStackTrace();}
	}
	static void addNewUser(List<Tweet> tweets, List<TweetPair> tweetPairs){
		/**/System.out.println(tweets.size()+"\t"+tweetPairs.size());
		
		if(tweets.size()<=1){return;}
		for(int i = 0;i < tweets.size()-1;++i){
			for(int j = i+1;
					j <= Math.min(tweets.size()-1, i + continuous_step);
					++j){
				Tweet ti = tweets.get(i);
				Tweet tj = tweets.get(j);
				TweetPair pair = new TweetPair();
				pair.setUserID(ti.userID);
				pair.setOldTweetID(ti.tweetID);
				pair.setOldTweetText(ti.actualTweet);
				pair.setOldTweetDate(ti.tweetDate);
				pair.setNewTweetID(tj.tweetID);
				pair.setNewTweetText(tj.actualTweet);
				pair.setNewTweetDate(tj.tweetDate);
				tweetPairs.add(pair);
			}
		}
		
		/**/System.out.println(tweetPairs.size()+"\n");
	}
	public static class Tweet{
		public long userID;
		public long tweetID;
		public String actualTweet;
		public java.util.Date tweetDate;
		public Tweet(long u, long t, String a, java.util.Date td){
			this.userID = u;
			this.tweetID = t;
			this.actualTweet = a;
			this.tweetDate = td;
		}
	}
	
	
	
	
	
	
	
	
	
	
	/**
	 * Get the typo pairs
	 */
	public static void getTypoPairs(){
		try{
			System.out.println("GET TYPO PAIRS");
			localConn = new DbConn(localHost,localDb,localUser,null);
			
			String query = 
				"SELECT " +
					"t.* " +
				"FROM " +
					"deltweet d, tweetpair t " +
				"WHERE " +
					"d.tweetID = t.oldTweetID ";
			ResultSet rs = localConn.executeQuery(query);
			
			List<TweetPairDistinct> typoTweetPairs = new ArrayList<TweetPairDistinct>();
			List<TweetPairDistinct> repostTweetPairs = new ArrayList<TweetPairDistinct>();
			while(rs.next()){
				String s1 = rs.getString("oldTweetText");
				String s2 = rs.getString("newTweetText");
				
				if(TypoCheck.isTypo(s1, s2)){
					TweetPairDistinct t = new TweetPairDistinct();
					t.setUserID(rs.getLong("userID"));
					t.setOldTweetID(rs.getLong("oldTweetID"));
					t.setOldTweetDate(sdf.parse(rs.getString("oldTweetDate")));
					t.setOldTweetText(rs.getString("oldTweetText"));
					t.setNewTweetID(rs.getLong("newTweetID"));
					t.setNewTweetDate(sdf.parse(rs.getString("newTweetDate")));
					t.setNewTweetText(rs.getString("newTweetText"));
					typoTweetPairs.add(t);
				}else if(TypoCheck.isRepost(s1, s2)){
					TweetPairDistinct t = new TweetPairDistinct();
					t.setUserID(rs.getLong("userID"));
					t.setOldTweetID(rs.getLong("oldTweetID"));
					t.setOldTweetDate(sdf.parse(rs.getString("oldTweetDate")));
					t.setOldTweetText(rs.getString("oldTweetText"));
					t.setNewTweetID(rs.getLong("newTweetID"));
					t.setNewTweetDate(sdf.parse(rs.getString("newTweetDate")));
					t.setNewTweetText(rs.getString("newTweetText"));
					repostTweetPairs.add(t);
				}
			}
			localConn.close();
			
			System.out.println("\nWrite to local");
			
			String jdbcURL = "jdbc:mysql://"+localHost+"/"+localDb;
			
			
			
			TypoTweetDAO tdao = new TypoTweetDAO(jdbcURL,localUser,null);
			for(int i = 0;i < typoTweetPairs.size();++i){
				try{
					tdao.create(typoTweetPairs.get(i));
				}catch(Exception e){
					e.printStackTrace(System.out);
				}
			}
			RepostTweetDAO rdao = new RepostTweetDAO(jdbcURL,localUser,null);
			for(int i = 0;i < repostTweetPairs.size();++i){
				try{
					rdao.create(repostTweetPairs.get(i));
				}catch(Exception e){
					e.printStackTrace(System.out);
				}
			}
			
			
			System.out.println("\nDone");
			
		}catch(Exception e){e.printStackTrace();}
	}

}
